Мир вокруг нас полон иерархических структур. Зачастую при разработке форм в Microsoft Excel мы сталкиваемся с задачей организации выбора показателей, имеющих некую иерархию, например, адреса (страна-город-улица-дом), организационная структура предприятия (департамент – управление – отдел-сотрудник) или же номенклатура товаров магазина. Почти каждый пользователь Excel умеет создавать в ячейке выпадающий список, более продвинутые могут создать второй, связанный с первым выпадающий список.
А можно ли создавать более сложные системы списков в Excel? На самом деле да, причем количество уровней таких списков ничем не ограничено и определяется только вашей потребностью. Рассмотрим методику, которая позволяет быстро и без особого труда строить многоуровневые выпадающие списки.
Основное преимущество данной методики состоит в том, что полученная система является полностью динамической, т.е. в неё можно добавить любые данные, даже целые ветви иерархии, и они автоматически попадут в выпадающие списки.
Основа методики – организация исходной информации в «Умных таблицах», т.е. специально структурированных объектах Excel. «Умную таблицу» можно создать клавишами Ctrl+T или кнопкой Таблица на вкладке Вставка, после чего таблице необходимо задать имя (во всплывающей при её выделении вкладки Конструктор). Основное требование – названия нижестоящих таблиц должны соответствовать элементам вышестоящих. В качестве примера рассмотрим фрагмент 6-уровневой иерархии товаров магазина, оформленной в “Умных таблицах”:
Остальные данные организуются по такому же принципу. После того, как данные организованы, необходимо непосредственно создать выпадающие списки, которые задаются специальными формулами, вводимыми в поле Источник в меню Проверка данных – Список:
Всего возможны три вида выпадающих списков:
1 уровень (первичный, не связанный ни с чем список)
2 уровень (имеющий одну связь на один вышестоящий выпадающий список: имя таблицы или имя поля)
3 уровень (имеющий две связи на вышестоящие списки: на имя таблицы и имя поля)
Обратите внимание! Все уровни иерархии моделируются с использованием этих трёх видов выпадающих списков, сгруппированных в различных комбинациях.
Итак, вот формулы, которые подставляются в Проверку данных в зависимости от конкретной ситуации. Обратите внимание, вместо красного текста подставляются непосредственно названия таблиц и полей, а вместо оранжевого – ставится ссылка на выпадающий список вышестоящего уровня:
Выпадающий список 1 уровня:
Создать список конкретных умных таблиц книги: Таблица1;Таблица2;Таблица3
Создать список полей конкретной таблицы: =ДВССЫЛ(“Таблица1“&”[#Заголовки]”)
Создать список элементов конкретного поля конкретной таблицы: =ДВССЫЛ(“Таблица1“&”[Поле1]”)
Выпадающий список 2 уровня:
Создать список полей таблицы, выбираемой в вышестоящем списке: =ДВССЫЛ(A1&”[#Заголовки]”)
Создать список элементов выбираемого поля конкретной таблицы: =ДВССЫЛ(“Таблица1“&”[“&А1&”]”)
Выпадающий список 3 уровня
Создать список элементов выбираемого поля выбираемой таблицы: =ДВССЫЛ(A1&”[“&B1&”]”)
Формулы можно задать вручную, что, однако, достаточно трудоемко. Лучше всего воспользоваться специальной группой команд Выпадающие списки в нашей надстройке PowerQuick для Excel, которая моментально сформирует за вас нужные формулы через специальные формы. Вам останется только скопировать нужные формулы в меню Проверка данных Excel.
Ну а если у вас есть трудности с организацией исходных данных и построением выпадающих списков, вы всегда можете получить консультацию разработчиков!